"""
Author: Bu Xiangfeng
E-mail: xiangfeng_006@foxmail.com
Date: 2018-10-25
"""

import pymysql


class ReadDataFromSQL(object):
    def __init__(self, db_name='test'):
        self._conn = pymysql.connect(host='135.251.227.21',
                                     port=3306,
                                     user='root',
                                     passwd='root',
                                     db=db_name,
                                     charset='utf8'
                                     )

        self._cursor = self._conn.cursor()
        self._cursor = self._conn.cursor(cursor=pymysql.cursors.DictCursor)
        self._user_table_name = None
        self.need_new_table()

    def read_data_random(self, limit_num=10, fail_record=None):
        if not fail_record:
            cmd = "SELECT * FROM  questions  ORDER BY RAND() LIMIT %s" %  limit_num
        else:
            cmd = "select * from questions  where id in (select id from (select * from %s order by fail_count  " \
                  "desc limit %s ) as t)" % (self._user_table_name, limit_num)

        self._cursor.execute(cmd)
        self._conn.commit()
        record = self._cursor.fetchall()
        return record

    def update_count(self, col_name, index):
        self._cursor.execute('update {0} set {1}={1} + 1 where id={2}'.format(self._user_table_name, col_name, index))
        self._conn.commit()

    def conn_exit(self):
        self._cursor.close()
        self._conn.close()

    def insert_data(self, key_list, value_list):
        cmd = "replace into %s (%s) VALUES ('%s')" % (self._user_table_name, ",".join(key_list), "','".join(value_list))
        self._cursor.execute(cmd)
        self._conn.commit()

    def clean_table(self):
        cmd = "delete from %s" % self._user_table_name
        self._cursor.execute(cmd)
        self._conn.commit()

    def create_table(self, table_name):
        cmd = "CREATE TABLE `%s` (`id` int(255) NOT NULL AUTO_INCREMENT, `pass_count` int(255) unsigned" \
              " zerofill DEFAULT '0', `fail_count` int(255) unsigned zerofill DEFAULT '0', PRIMARY KEY (`id`)) " \
              "ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;" % table_name
        self._cursor.execute(cmd)
        self._conn.commit()

    def drop_table(self):
        cmd = "drop table %s" % self._user_table_name
        self._cursor.execute(cmd)
        self._conn.commit()

    def need_new_table(self):
        table_name = input("Your user name:").strip()
        if not table_name:
            table_name = "guest_table"
        else:
            table_name += '_table'
        self._user_table_name = table_name

        try:
            self.create_table(table_name)
            self._user_table_name = table_name
            keys = ["pass_count", 'fail_count']
            values = ['0', '0']
            for i in range(1, 201):
                self.insert_data(key_list=keys, value_list=values)
            print("the table has been created ")
        except:
            pass


if __name__ == "__main__":

    tt = ReadDataFromSQL()
    # result = tt.read_data_random()
    # print(result)
    tt.conn_exit()

